<?php

namespace app\vip_agent\controller;

use think\Controller;
use think\Db;
use think\Request;
use think\Session;

class Vip extends Base
{
    //
    public function index()
    {
        $list = Db::name('agents')->field(true,['password'])->where('vid',Session::get('user.id'))->paginate(10);
        $page = $list->render();

        return view('vip/index',['list'=>$list,'page'=>$page]);
    }

    public function showCustomer(Request $request)
    {

        $id=$request->get('id');
        $name=Db::name('users')->where('id',$id)->find()['name'];
        $list = Db::name('users')->where(['aid'=>$id])
            ->paginate(15,false,['query'=>$_GET]);
        $page=$list->render();
        $list=$list->items();
        foreach ($list as $k=>$v){
            if(strlen($v['name'])==11){
                $list[$k]['name']=substr_replace($v['name'], '*****', 3, 5);
            }else{
                $list[$k]['name']=substr_replace($v['name'], '*', 3, 3);
            }
            $list[$k]['phonenumber']=substr_replace($v['phonenumber'], '*****', 3, 5);
        }

        return view('vip/showCustomer',['list'=>$list,'name'=>$name,'page'=>$page]);

    }

    public function show(Request $request)
    {
        $id=$request->get('id');
        $user=Db::name('users')->field('name,assetaccount,fwq')->find($id);
        $qjgj = Db::name('futures_api')->where(['id'=>$user['fwq']])->find();
        if($qjgj['title']=='jt'){
            $req['Action']='ReqQryAccountInfo';
            $req['ChdAccountID']=$user['assetaccount'];
            $req['UserID']=$qjgj['sa'];
            ksort($req);
            $url='';
            foreach ($req as $k=>$v){
                $url=$url.$k.'='.$v.'&';
            }
            $req=rtrim($url,'&');
            //var_dump($req);
            $sign=md5($req.'&UserKey='.$qjgj['sapass']);
            $req=$req.'&Sign='.$sign;
            //var_dump($req);
            $url=$qjgj['api'].'?'.$req;
            // var_dump($url);
            $res=https_request($url);
            //var_dump($res);die;
            //var_dump(json_decode($res,true));die;
            $data=json_decode($res,true);
            if(strlen($user['name'])==11){
                $user['name']=substr_replace($user['name'], '*****', 3, 5);
            }else{
                $user['name']=substr_replace($user['name'], '*', 3, 3);
            }
            $da = [];
            if($data['ErrorID']==0) {
                $da['dqqy'] = $data['LowAvailable']/100;//劣后
                $da['Credit'] = $data['HighBalance']/100;//优先
                $da['Margin']=$data['CurrMargin'];//保证金
                $da['Balance'] = $data['Balance']/100;//当前权益
                $da['Available'] = $data['Available']/100;//可用资金
//                $data['']=$data['BadAmount'];
                return ['status'=>1,'res'=>$da,'name'=>$user['name']];
            }else{
                return ['status'=>2,'msg'=>$data['ErrorMsg'],'name'=>$user['name']];
            }

        }else{
            $url="{$qjgj['api']}queryaccount?requestid=6&sa={$qjgj['sa']}&sapass={$qjgj['sapass']}&account={$user['assetaccount']}";
            $data=https_request($url);
            $objectxml = simplexml_load_string($data);//将文件转换成 对象
            $xmljson= json_encode($objectxml );//将对象转换个JSON
            $xmlarray=json_decode($xmljson,true);//将json转换成数组
            if(strlen($user['name'])==11){
                $res['name']=substr_replace($user['name'], '*****', 3, 5);
            }else{
                $res['name']=substr_replace($user['name'], '*', 3, 3);
            }
            if($xmlarray['Result']['Error']['Code']!=0){
                return ['status'=>2,'msg'=>$xmlarray['Result']['Error']['Message'],'name'=>$user['name']];
            }else{
                return ['status'=>1,'res'=>$xmlarray['Result']['Summary'],'name'=>$user['name']];
            }
        }
        return $res;
    }

    // 企业认证
    public function realname()
    {
        $id=Session::get('user.id');
        $list=Db::name('user_certification')->where('uid',$id)->find();
        if(!$list){
            $list['name']='';
            $list['IDnumber']='';
            $list['IDpositive']='';
            $list['IDback']='';
            $list['status']=3;
        }


        return view('vip/realname',['list'=>$list]);

    }

    public function doeditrealname(Request $request)
    {
        $post=$request->post();
        $file = request()->file('IDpositive');
        if($file){
            $info = $file->move(ROOT_PATH . 'public' . DS . 'upload/personal');
            if($info){
                $post['IDpositive']=date('Ymd').'/'.$info->getFilename();
            }else{
                return "<script>alert('".$file->getError()."');history.go(-1)</script>";
            }
        }
        $file = request()->file('IDback');
        if($file){
            $info = $file->move(ROOT_PATH . 'public' . DS . 'upload/personal');
            if($info){
                $post['IDback']=date('Ymd').'/'.$info->getFilename();
            }else{
                return "<script>alert('".$file->getError()."');history.go(-1)</script>";
            }
        }
        $post['createtime']=date('Y-m-d H:i:s');
        $post['status']=0;

        $id=Session::get('user.id');
        $post['uid']=$id;
        $list=Db::name('user_certification')->where('uid',$id)->find();
        if($list){
            $res=Db::name('user_certification')->where('uid',$id)->update($post);
        }else{
            $res=Db::name('user_certification')->insert($post);
        }



        if(!$res){
//                    return json_encode(['msg'=>'注册失败'],JSON_UNESCAPED_UNICODE);
            return "<script>alert('更新失败');history.go(-1)</script>";
        }else{
//                    return json_encode(['msg'=>'success'],JSON_UNESCAPED_UNICODE);
            return "<script>alert('更新成功,正在审核中');window.location.href='".url('vip_agent/Vip/realname')."'</script>";
        }
    }

    // 交易记录
    public function showlist(Request $request)
    {
            $get=myarr($request->get());
            $get['p']=isset($get['p'])?$get['p']:config('paginate.list_rows');
            $get['vip']=isset($get['vip'])?Session::get('user.id'):Session::get('user.id');
            $get['agent']=isset($get['agent'])?$get['agent']:'';
            $get['kp']=isset($get['kp'])?$get['kp']:'';
            $get['mm']=isset($get['mm'])?$get['mm']:'';
            $get['hy']=isset($get['hy'])?$get['hy']:'';
            $get['zzh']=isset($get['zzh'])?$get['zzh']:'';
            $get['start']=isset($get['start'])?$get['start']:date('Y-m-d',strtotime('-1 day'));
            $get['end']=isset($get['end'])?$get['end']:date('Y-m-d');
            $jystart=str_replace('-','',$get['start']);
            $jyend=str_replace('-','',$get['end']);

            $allvip=Db::name('vip')->field('id,name')->select();
            if($get['vip']!=''){
                $allagent=Db::name('agents')->field('id,name')->where('vid',Session::get('user.id'))->select();
                $where['vip']=['=',$get['vip']];
                if($get['agent']!=''){
                    $where['agents']=['=',$get['agent']];
                }
            }else{
                $allagent=[];
            }
            if($get['kp']!=''){
                $where['Kaiping']=['=',$get['kp']];
            }
            if($get['mm']!=''){
                $where['Business']=['=',$get['mm']];
            }
            if($get['hy']!=''){
                $where['Contract']=['=',$get['hy']];
            }
            if($get['zzh']!=''){
                $where['Subaccount']=['=',$get['zzh']];
            }
            if($get['start']!=''&&$get['end']!=''){
                $where['TradingDday']=['between',[$jystart,$jyend]];
            }
            $list=Db::name('transaction')
                ->field(true)
                ->where($where)
                ->order('id desc')
//            ->select();
                ->paginate($get['p'],false,['query'=>$get]);
            $page=$list->render();
            $list=$list->items();
            $count['Count']=Db::name('transaction')
                ->field(true)
                ->where($where)
                ->sum('Count');
            $count['Poundage']=Db::name('transaction')
                ->field(true)
                ->where($where)
                ->sum('Poundage');
        $Float=Db::name('transaction')
            ->alias('a')
            ->join('lc_users b','a.uid=b.id')
            ->field('a.*,b.name,b.phonenumber')
//            ->field(true)
            ->where($where)
            ->column('`Float`');
        $count['Float'] = 0;
        foreach ($Float as $v){
            $count['Float'] += $v;
        }
        $Profit=Db::name('transaction')
            ->alias('a')
            ->join('lc_users b','a.uid=b.id')
            ->field('a.*,b.name,b.phonenumber')
//            ->field(true)
            ->where($where)
            ->column('`Profit`');
        $count['Profit'] = 0;
        foreach ($Profit as $v){
            $count['Profit'] += $v;
        }
            $count['Brokerage']=Db::name('transaction')
                ->field(true)
                ->where($where)
                ->sum('Brokerage');

            return view('transaction/showlist',['list'=>$list,'page'=>$page,'get'=>$get,'allvip'=>$allvip,'allagent'=>$allagent,'count'=>$count]);
    }

    public function xq(Request $request)
    {
        $id=$request->get('id');
        $list=Db::name('transaction')->find($id);
        return $list;
    }

    // 佣金汇总 日期
    public function showsum(Request $request){
        $get['start']=isset($get['start'])?$get['start']:date('Y-m-d',strtotime('monday last week'));
        $get['end']=isset($get['end'])?$get['end']:date('Y-m-d',strtotime("friday last week"));
        return view('vip/showsum',['get'=>$get]);
    }
    // 佣金汇总 数据
    public function showsummary(Request $request)
    {
        $tempid=session('user.id');
        $get=$request->get();
        $get['start']=isset($get['start'])?$get['start']:date('Y-m-d',strtotime('monday last week'));
        $get['end']=isset($get['end'])?$get['end']:date('Y-m-d',strtotime("friday last week"));
        $now=date('Y-m-d');
        $c=(strtotime($now)-strtotime($get['start']))/86400;
        if($c>=67){
            return "<script>alert('只支持60天内数据的查询');history.go(-1)</script>";
        }
        $jystart=str_replace('-','',$get['start']);
        $jyend=str_replace('-','',$get['end']);
        $viplist=Db::name('users')->field('id,name')->where('register',2)->where('leaguer',$tempid)->paginate(15,false,['query'=>$get]);
        $page=$viplist->render();
        $list=$viplist->items();
        foreach ($list as $k=>$v){
//            $list[$k]['Count']=Db::name('transaction')->where('vip',$v['id'])->sum('Count');
//            $list[$k]['Poundage']=Db::name('transaction')->where('vip',$v['id'])->sum('Poundage');
//            $list[$k]['Profit']=Db::name('transaction')->where('vip',$v['id'])->sum('Profit');
//            $list[$k]['Brokerage']=Db::name('transaction')->where('vip',$v['id'])->sum('Brokerage');
            $list[$k]['tj']=Db::name('transaction')
                ->field('sum(Count) Count,sum(Poundage) Poundage,sum(Profit) Profit,sum(Brokerage) Brokerage')
                ->where('TradingDday','between',[$jystart,$jyend])
                ->where('agents',$v['id'])
                ->find();
        }
        foreach ($list as $k=>$v){
            if($v['tj']['Poundage']==0){
                $list[$k]['tj']['fj']='';
                continue;
            }
            $list[$k]['tj']['fj']=$v['tj']['Poundage']-$v['tj']['Brokerage'];
        }
        $count['Count']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->where('vip',$tempid)->sum('Count');
        $count['Poundage']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->where('vip',$tempid)->sum('Poundage');

        $count['Profit']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->where('vip',$tempid)->sum('Profit');


        $count['Brokerage']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->where('vip',$tempid)->sum('Brokerage');
        $count['fj']=$count['Poundage']-$count['Brokerage'];
        return view('vip/showsummary',['list'=>$list,'page'=>$page,'get'=>$get,'count'=>$count]);
    }
    // 佣金汇总 导出
    public function export(Request $request)
    {
        $tempid=session('user.id');
        $get['start']=$request->get('start');
        $get['end']=$request->get('end');
        $jystart=str_replace('-','',$get['start']);
        $jyend=str_replace('-','',$get['end']);
//        var_dump($get);
        $now=date('Y-m-d');
        $c=(strtotime($now)-strtotime($get['start']))/86400;
        if($c>=21){
            return "<script>alert('只支持两周数据的导出');history.go(-1)</script>";
        }
        $list=Db::name('users')->field('id,name')->where('register',2)->where('leaguer',$tempid)->select();
        foreach ($list as $k=>$v){
//            $list[$k]['Count']=Db::name('transaction')->where('vip',$v['id'])->sum('Count');
//            $list[$k]['Poundage']=Db::name('transaction')->where('vip',$v['id'])->sum('Poundage');
//            $list[$k]['Profit']=Db::name('transaction')->where('vip',$v['id'])->sum('Profit');
//            $list[$k]['Brokerage']=Db::name('transaction')->where('vip',$v['id'])->sum('Brokerage');
            $temp=Db::name('transaction')
                ->field('sum(Count) Count,sum(Poundage) Poundage,sum(Profit) Profit,sum(Brokerage) Brokerage')
                ->where('TradingDday','between',[$jystart,$jyend])
                ->where('agents',$v['id'])
                ->find();
            if($temp['Poundage']==0){
                $list[$k]['fj']='';
                continue;
            }
            $list[$k]=array_merge($list[$k],$temp);
            $list[$k]['fj']=$list[$k]['Poundage']-$list[$k]['Brokerage'];
        }

        $count[0]['id']='总计';
        $count[0]['name']='';
        $count[0]['Count']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->where('vip',$tempid)->sum('Count');
        $count[0]['Poundage']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->where('vip',$tempid)->sum('Poundage');
        $count[0]['Profit']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->where('vip',$tempid)->sum('Profit');
        $count[0]['Brokerage']=Db::name('transaction')->where('TradingDday','between',[$jystart,$jyend])->where('vip',$tempid)->sum('Brokerage');
        $count[0]['fj']=$count[0]['Poundage']-$count[0]['Brokerage'];

        $list=array_merge($list,$count);

//        $fileName .= "_" . date("Y_m_d", Request::instance()->time()) . ".xls";
        $fileName=$jystart.'-'.$jyend.'佣金汇总'.'.xls';
        $headArr=['代理商id','代理商名称','成交量','手续费','平仓盈亏','基础手续费','附加手续费'];
        $data=$list;
        $objPHPExcel = new \PHPExcel();

        $objPHPExcel->getProperties();

        $key = ord("A"); // 设置表头

        foreach ($headArr as $v) {

            $colum = chr($key);

            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);

            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);

            $key += 1;

        }

        $column = 2;

        $objActSheet = $objPHPExcel->getActiveSheet();

        foreach ($data as $key => $rows) { // 行写入

            $span = ord("A");

            foreach ($rows as $keyName => $value) { // 列写入

                $objActSheet->setCellValue(chr($span) . $column, $value);

                $span++;

            }

            $column++;

        }

        $fileName = iconv("utf-8", "gb2312", $fileName); // 重命名表

        $objPHPExcel->setActiveSheetIndex(0); // 设置活动单指数到第一个表,所以Excel打开这是第一个表

        header('Content-Type: application/vnd.ms-excel');

        header("Content-Disposition: attachment;filename='$fileName'");

        header('Cache-Control: max-age=0');

        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

        $objWriter->save('php://output'); // 文件通过浏览器下载

        exit();

    }





}
